using Microsoft.Extensions.Logging;
using SeataNet.Core.AT.UndoLog;
using System.Data;
using System.Data.Common;
using System.Text;
using System.Text.RegularExpressions;

namespace SeataNet.Core.Database.PostgreSQL
{
    /// <summary>
    /// PostgreSQL数据库方言
    /// </summary>
    public class PostgreSqlDialect : IDatabaseDialect
    {
        private readonly ILogger<PostgreSqlDialect> _logger;

        public PostgreSqlDialect(ILogger<PostgreSqlDialect> logger)
        {
            _logger = logger;
        }

        public DatabaseType DatabaseType => DatabaseType.PostgreSQL;

        public async Task<TableMeta> GetTableMetaAsync(DbConnection connection, string tableName)
        {
            _logger.LogDebug($"获取PostgreSQL表元数据: {tableName}");

            var tableMeta = new TableMeta
            {
                TableName = tableName
            };

            // 获取列信息
            var columnsSql = @"
                SELECT
                    c.column_name,
                    c.data_type,
                    c.is_nullable,
                    CASE WHEN pk.column_name IS NOT NULL THEN 'PRI' ELSE '' END as column_key
                FROM information_schema.columns c
                LEFT JOIN (
                    SELECT ku.column_name
                    FROM information_schema.table_constraints tc
                    JOIN information_schema.key_column_usage ku
                        ON tc.constraint_name = ku.constraint_name
                        AND tc.table_schema = ku.table_schema
                    WHERE tc.constraint_type = 'PRIMARY KEY'
                        AND tc.table_schema = 'public'
                        AND tc.table_name = @TableName
                ) pk ON c.column_name = pk.column_name
                WHERE c.table_schema = 'public'
                AND c.table_name = @TableName
                ORDER BY c.ordinal_position";

            using (var command = connection.CreateCommand())
            {
                command.CommandText = columnsSql;
                var param = command.CreateParameter();
                param.ParameterName = "@TableName";
                param.Value = tableName;
                command.Parameters.Add(param);

                using (var reader = await command.ExecuteReaderAsync())
                {
                    while (await reader.ReadAsync())
                    {
                        var columnName = reader.GetString(0);
                        var dataType = reader.GetString(1);
                        var isNullable = reader.GetString(2) == "YES";
                        var columnKey = reader.IsDBNull(3) ? "" : reader.GetString(3);
                        var isPrimaryKey = columnKey == "PRI";

                        tableMeta.Columns[columnName] = new ColumnMeta
                        {
                            ColumnName = columnName,
                            DataType = dataType,
                            IsNullable = isNullable,
                            IsPrimaryKey = isPrimaryKey
                        };

                        if (isPrimaryKey)
                        {
                            tableMeta.PrimaryKeyColumns.Add(columnName);
                        }
                    }
                }
            }

            _logger.LogDebug($"PostgreSQL表元数据加载完成: {tableName}, 列数: {tableMeta.Columns.Count}, 主键: {string.Join(", ", tableMeta.PrimaryKeyColumns)}");

            return tableMeta;
        }

        public string BuildBeforeImageSql(SqlRecognizeResult recognizeResult, TableMeta tableMeta)
        {
            if (recognizeResult.SqlType == SqlType.INSERT)
            {
                // INSERT操作不需要before image
                return null;
            }

            var columns = string.Join(", ", tableMeta.Columns.Keys.Select(c => $"\"{c}\""));
            var tableName = $"\"{recognizeResult.TableName}\"";

            if (recognizeResult.SqlType == SqlType.UPDATE || recognizeResult.SqlType == SqlType.DELETE)
            {
                // 使用原SQL的WHERE条件
                var whereClause = recognizeResult.WhereCondition ?? "1=1";
                return $"SELECT {columns} FROM {tableName} WHERE {whereClause} FOR UPDATE";
            }

            return null;
        }

        public string BuildAfterImageSql(SqlRecognizeResult recognizeResult, TableMeta tableMeta)
        {
            if (recognizeResult.SqlType == SqlType.DELETE)
            {
                // DELETE操作不需要after image
                return null;
            }

            var columns = string.Join(", ", tableMeta.Columns.Keys.Select(c => $"\"{c}\""));
            var tableName = $"\"{recognizeResult.TableName}\"";

            if (recognizeResult.SqlType == SqlType.INSERT || recognizeResult.SqlType == SqlType.UPDATE)
            {
                // 使用主键条件或WHERE条件
                if (!string.IsNullOrEmpty(recognizeResult.WhereCondition))
                {
                    return $"SELECT {columns} FROM {tableName} WHERE {recognizeResult.WhereCondition}";
                }
                else if (tableMeta.PrimaryKeyColumns.Any())
                {
                    // 对于INSERT，PostgreSQL可以使用RETURNING子句，但这里返回基础查询
                    // 实际使用时需要添加主键条件
                    return $"SELECT {columns} FROM {tableName} WHERE 1=1";
                }
            }

            return null;
        }

        public ISqlRecognizer GetSqlRecognizer(string sql)
        {
            return new PostgreSqlRecognizer(sql, _logger);
        }
    }

    /// <summary>
    /// PostgreSQL SQL识别器
    /// </summary>
    public class PostgreSqlRecognizer : ISqlRecognizer
    {
        private readonly string _originalSql;
        private readonly ILogger _logger;
        private SqlRecognizeResult _result;

        public PostgreSqlRecognizer(string sql, ILogger logger)
        {
            _originalSql = sql?.Trim();
            _logger = logger;
        }

        public SqlType GetSqlType()
        {
            if (string.IsNullOrEmpty(_originalSql))
            {
                return SqlType.SELECT_FOR_UPDATE;
            }

            var upperSql = _originalSql.ToUpperInvariant();

            if (upperSql.StartsWith("INSERT"))
                return SqlType.INSERT;
            if (upperSql.StartsWith("UPDATE"))
                return SqlType.UPDATE;
            if (upperSql.StartsWith("DELETE"))
                return SqlType.DELETE;

            return SqlType.SELECT_FOR_UPDATE;
        }

        public string GetTableName()
        {
            var sqlType = GetSqlType();

            try
            {
                switch (sqlType)
                {
                    case SqlType.INSERT:
                        // INSERT INTO table_name or INSERT INTO "table_name"
                        var insertMatch = Regex.Match(_originalSql, @"INSERT\s+INTO\s+""?(\w+)""?", RegexOptions.IgnoreCase);
                        return insertMatch.Success ? insertMatch.Groups[1].Value : null;

                    case SqlType.UPDATE:
                        // UPDATE table_name or UPDATE "table_name"
                        var updateMatch = Regex.Match(_originalSql, @"UPDATE\s+""?(\w+)""?", RegexOptions.IgnoreCase);
                        return updateMatch.Success ? updateMatch.Groups[1].Value : null;

                    case SqlType.DELETE:
                        // DELETE FROM table_name or DELETE FROM "table_name"
                        var deleteMatch = Regex.Match(_originalSql, @"DELETE\s+FROM\s+""?(\w+)""?", RegexOptions.IgnoreCase);
                        return deleteMatch.Success ? deleteMatch.Groups[1].Value : null;

                    default:
                        return null;
                }
            }
            catch (Exception ex)
            {
                _logger?.LogWarning(ex, $"解析表名失败: {_originalSql}");
                return null;
            }
        }

        public SqlRecognizeResult Recognize()
        {
            if (_result != null)
            {
                return _result;
            }

            _result = new SqlRecognizeResult
            {
                OriginalSql = _originalSql,
                SqlType = GetSqlType(),
                TableName = GetTableName()
            };

            try
            {
                switch (_result.SqlType)
                {
                    case SqlType.INSERT:
                        RecognizeInsert();
                        break;

                    case SqlType.UPDATE:
                        RecognizeUpdate();
                        break;

                    case SqlType.DELETE:
                        RecognizeDelete();
                        break;
                }
            }
            catch (Exception ex)
            {
                _logger?.LogWarning(ex, $"SQL识别失败: {_originalSql}");
            }

            return _result;
        }

        private void RecognizeInsert()
        {
            // INSERT INTO table (col1, col2) VALUES (?, ?) or with quotes
            var match = Regex.Match(_originalSql, @"INSERT\s+INTO\s+""?\w+""?\s*\(([^)]+)\)", RegexOptions.IgnoreCase);
            if (match.Success)
            {
                var columnsStr = match.Groups[1].Value;
                _result.InsertColumns = columnsStr
                    .Split(',')
                    .Select(c => c.Trim().Trim('"'))
                    .ToList();
            }
        }

        private void RecognizeUpdate()
        {
            // UPDATE table SET col1 = ?, col2 = ? WHERE condition

            // 提取SET子句的列
            var setMatch = Regex.Match(_originalSql, @"SET\s+(.+?)(?:WHERE|$)", RegexOptions.IgnoreCase);
            if (setMatch.Success)
            {
                var setClause = setMatch.Groups[1].Value;
                var assignments = Regex.Matches(setClause, @"""?(\w+)""?\s*=");

                foreach (Match assignment in assignments)
                {
                    _result.UpdateColumns.Add(assignment.Groups[1].Value);
                }
            }

            // 提取WHERE子句
            var whereMatch = Regex.Match(_originalSql, @"WHERE\s+(.+)$", RegexOptions.IgnoreCase);
            if (whereMatch.Success)
            {
                _result.WhereCondition = whereMatch.Groups[1].Value.Trim();
            }
        }

        private void RecognizeDelete()
        {
            // DELETE FROM table WHERE condition
            var whereMatch = Regex.Match(_originalSql, @"WHERE\s+(.+)$", RegexOptions.IgnoreCase);
            if (whereMatch.Success)
            {
                _result.WhereCondition = whereMatch.Groups[1].Value.Trim();
            }
        }
    }
}
